using ClosedXML.Excel;
using System.Text;
using WeatherSystem.API.Models;
using WeatherSystem.API.Repositories;
using WeatherSystem.API.Extensions;

namespace WeatherSystem.API.Services
{
    /// <summary>
    /// Data export service interface
    /// </summary>
    public interface IExportService
    {
        Task<byte[]> ExportWeatherDataToExcelAsync(string? cityId = null, string? startDate = null, string? endDate = null, string[]? columns = null);
        Task<byte[]> ExportWeatherDataToCsvAsync(string? cityId = null, string? startDate = null, string? endDate = null, string[]? columns = null);
        Task<byte[]> ExportStatisticsToExcelAsync(string? cityId = null, string? startDate = null, string? endDate = null);
        Task<byte[]> ExportCitiesToExcelAsync();
        Task<byte[]> ExportCitiesToCsvAsync();
        Task<byte[]> ExportStatisticsToCsvAsync(string? cityId = null, string? startDate = null, string? endDate = null);
    }

    /// <summary>
    /// Data export service implementation
    /// </summary>
    public class ExportService : IExportService
    {
        private readonly IWeatherDataRepository _weatherRepository;
        private readonly IStatisticsRepository _statisticsRepository;
        private readonly ICityRepository _cityRepository;
        private readonly ILogger<ExportService> _logger;

        public ExportService(
            IWeatherDataRepository weatherRepository,
            IStatisticsRepository statisticsRepository,
            ICityRepository cityRepository,
            ILogger<ExportService> logger)
        {
            _weatherRepository = weatherRepository;
            _statisticsRepository = statisticsRepository;
            _cityRepository = cityRepository;
            _logger = logger;
        }

        public async Task<byte[]> ExportWeatherDataToExcelAsync(string? cityId = null, string? startDate = null, string? endDate = null, string[]? columns = null)
        {
            try
            {
                // 解析参数
                Guid? parsedCityId = null;
                DateTime? parsedStartDate = null;
                DateTime? parsedEndDate = null;

                if (!string.IsNullOrEmpty(cityId) && Guid.TryParse(cityId, out var guid))
                {
                    parsedCityId = guid;
                }

                if (!string.IsNullOrEmpty(startDate) && DateTime.TryParse(startDate, out var start))
                {
                    parsedStartDate = start;
                }

                if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out var end))
                {
                    parsedEndDate = end;
                }

                var data = await _weatherRepository.GetWeatherDataPagedAsync(
                    1, 10000, parsedCityId, parsedStartDate, parsedEndDate);

                using var workbook = new XLWorkbook();
                var worksheet = workbook.Worksheets.Add("天气数据");

                // 写入表头
                var headers = GetWeatherDataHeaders(data.Data);
                for (int i = 0; i < headers.Count; i++)
                {
                    worksheet.Cell(1, i + 1).Value = headers[i];
                    worksheet.Cell(1, i + 1).Style.Font.Bold = true;
                    worksheet.Cell(1, i + 1).Style.Fill.BackgroundColor = XLColor.LightGray;
                }

                // 写入数据
                int row = 2;
                foreach (var item in data.Data)
                {
                    int col = 1;
                    foreach (var header in headers)
                    {
                        var value = item.ContainsKey(header) ? item[header] : "";
                        worksheet.Cell(row, col).Value = value?.ToString() ?? "";
                        col++;
                    }
                    row++;
                }

                // 自动调整列宽
                worksheet.ColumnsUsed().AdjustToContents();

                // 添加筛选器
                var range = worksheet.Range(1, 1, row - 1, headers.Count);
                range.SetAutoFilter();

                using var stream = new MemoryStream();
                workbook.SaveAs(stream);
                return stream.ToArray();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出天气数据到Excel失败");
                throw;
            }
        }

        public async Task<byte[]> ExportWeatherDataToCsvAsync(string? cityId = null, string? startDate = null, string? endDate = null, string[]? columns = null)
        {
            try
            {
                // 解析参数
                Guid? parsedCityId = null;
                DateTime? parsedStartDate = null;
                DateTime? parsedEndDate = null;

                if (!string.IsNullOrEmpty(cityId) && Guid.TryParse(cityId, out var guid))
                {
                    parsedCityId = guid;
                }

                if (!string.IsNullOrEmpty(startDate) && DateTime.TryParse(startDate, out var start))
                {
                    parsedStartDate = start;
                }

                if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out var end))
                {
                    parsedEndDate = end;
                }

                var data = await _weatherRepository.GetWeatherDataPagedAsync(
                    1, 10000, parsedCityId, parsedStartDate, parsedEndDate);

                var headers = GetWeatherDataHeaders(data.Data);
                var csv = new StringBuilder();

                // 写入表头
                csv.AppendLine(string.Join(",", headers.Select(h => $"\"{h}\"")));

                // 写入数据
                foreach (var item in data.Data)
                {
                    var values = headers.Select(header =>
                    {
                        var value = item.ContainsKey(header) ? item[header] : "";
                        return $"\"{value?.ToString()?.Replace("\"", "\"\"") ?? ""}\"";
                    });
                    csv.AppendLine(string.Join(",", values));
                }

                return Encoding.UTF8.GetBytes(csv.ToString());
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出天气数据到CSV失败");
                throw;
            }
        }

        public async Task<byte[]> ExportStatisticsToExcelAsync(string? cityId = null, string? startDate = null, string? endDate = null)
        {
            try
            {
                // 解析参数
                Guid parsedCityId = Guid.Empty;
                DateTime? parsedStartDate = null;
                DateTime? parsedEndDate = null;

                if (!string.IsNullOrEmpty(cityId) && Guid.TryParse(cityId, out var guid))
                {
                    parsedCityId = guid;
                }

                if (!string.IsNullOrEmpty(startDate) && DateTime.TryParse(startDate, out var start))
                {
                    parsedStartDate = start;
                }

                if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out var end))
                {
                    parsedEndDate = end;
                }

                var data = await _statisticsRepository.GetStatisticsRangeAsync(parsedCityId, null, parsedStartDate ?? DateTime.MinValue, parsedEndDate ?? DateTime.MaxValue);

                using var workbook = new XLWorkbook();
                var worksheet = workbook.Worksheets.Add("统计数据");

                // 转换统计数据为字典格式
                var dataDicts = data.Select(d => d.ToDictionary()).ToList();

                // 获取所有统计类型的数据结构
                var allKeys = dataDicts.SelectMany(d => d.Keys).Distinct().ToList();
                
                // 写入表头
                for (int i = 0; i < allKeys.Count; i++)
                {
                    worksheet.Cell(1, i + 1).Value = allKeys[i];
                    worksheet.Cell(1, i + 1).Style.Font.Bold = true;
                    worksheet.Cell(1, i + 1).Style.Fill.BackgroundColor = XLColor.LightBlue;
                }

                // 写入数据
                int row = 2;
                foreach (var item in dataDicts)
                {
                    int col = 1;
                    foreach (var key in allKeys)
                    {
                        var value = item.ContainsKey(key) ? item[key] : "";
                        worksheet.Cell(row, col).Value = value?.ToString() ?? "";
                        col++;
                    }
                    row++;
                }

                worksheet.ColumnsUsed().AdjustToContents();
                var range = worksheet.Range(1, 1, row - 1, allKeys.Count);
                range.SetAutoFilter();

                using var stream = new MemoryStream();
                workbook.SaveAs(stream);
                return stream.ToArray();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出统计数据到Excel失败");
                throw;
            }
        }

        public async Task<byte[]> ExportCitiesToExcelAsync()
        {
            try
            {
                var cities = await _cityRepository.GetAllAsync();

                using var workbook = new XLWorkbook();
                var worksheet = workbook.Worksheets.Add("城市列表");

                // 表头
                var headers = new[] { "ID", "名称", "国家", "纬度", "经度", "时区", "是否活跃", "自动采集", "创建时间" };
                for (int i = 0; i < headers.Length; i++)
                {
                    worksheet.Cell(1, i + 1).Value = headers[i];
                    worksheet.Cell(1, i + 1).Style.Font.Bold = true;
                    worksheet.Cell(1, i + 1).Style.Fill.BackgroundColor = XLColor.LightGreen;
                }

                // 数据
                int row = 2;
                foreach (var city in cities)
                {
                    worksheet.Cell(row, 1).Value = city.Id.ToString();
                    worksheet.Cell(row, 2).Value = city.Name;
                    worksheet.Cell(row, 3).Value = city.Country;
                    worksheet.Cell(row, 4).Value = (double)city.Latitude;
                    worksheet.Cell(row, 5).Value = (double)city.Longitude;
                    worksheet.Cell(row, 6).Value = city.TimeZone;
                    worksheet.Cell(row, 7).Value = city.IsActive ? "是" : "否";
                    worksheet.Cell(row, 8).Value = city.AutoCollectEnabled ? "是" : "否";
                    worksheet.Cell(row, 9).Value = city.CreatedAt.ToString("yyyy-MM-dd HH:mm:ss");
                    row++;
                }

                worksheet.ColumnsUsed().AdjustToContents();

                using var stream = new MemoryStream();
                workbook.SaveAs(stream);
                return stream.ToArray();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出城市数据到Excel失败");
                throw;
            }
        }

        public async Task<byte[]> ExportCitiesToCsvAsync()
        {
            try
            {
                var cities = await _cityRepository.GetAllAsync();

                var csv = new StringBuilder();

                // 表头
                var headers = new[] { "ID", "名称", "国家", "纬度", "经度", "时区", "是否活跃", "自动采集", "创建时间" };
                csv.AppendLine(string.Join(",", headers.Select(h => $"\"{h}\"")));

                // 数据
                foreach (var city in cities)
                {
                    var values = new[]
                    {
                        $"\"{city.Id}\"",
                        $"\"{city.Name}\"",
                        $"\"{city.Country}\"",
                        $"\"{city.Latitude}\"",
                        $"\"{city.Longitude}\"",
                        $"\"{city.TimeZone}\"",
                        $"\"{(city.IsActive ? "是" : "否")}\"",
                        $"\"{(city.AutoCollectEnabled ? "是" : "否")}\"",
                        $"\"{city.CreatedAt:yyyy-MM-dd HH:mm:ss}\""
                    };
                    csv.AppendLine(string.Join(",", values));
                }

                return Encoding.UTF8.GetBytes(csv.ToString());
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出城市数据到CSV失败");
                throw;
            }
        }

        public async Task<byte[]> ExportStatisticsToCsvAsync(string? cityId = null, string? startDate = null, string? endDate = null)
        {
            try
            {
                // 解析参数
                Guid parsedCityId = Guid.Empty;
                DateTime? parsedStartDate = null;
                DateTime? parsedEndDate = null;

                if (!string.IsNullOrEmpty(cityId) && Guid.TryParse(cityId, out var guid))
                {
                    parsedCityId = guid;
                }

                if (!string.IsNullOrEmpty(startDate) && DateTime.TryParse(startDate, out var start))
                {
                    parsedStartDate = start;
                }

                if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out var end))
                {
                    parsedEndDate = end;
                }

                var data = await _statisticsRepository.GetStatisticsRangeAsync(parsedCityId, null, parsedStartDate ?? DateTime.MinValue, parsedEndDate ?? DateTime.MaxValue);

                var csv = new StringBuilder();

                // 转换统计数据为字典格式
                var dataDicts = data.Select(d => d.ToDictionary()).ToList();

                // 获取所有统计类型的数据结构
                var allKeys = dataDicts.SelectMany(d => d.Keys).Distinct().ToList();

                // 写入表头
                csv.AppendLine(string.Join(",", allKeys.Select(k => $"\"{k}\"")));

                // 写入数据
                foreach (var item in dataDicts)
                {
                    var values = allKeys.Select(key =>
                    {
                        var value = item.ContainsKey(key) ? item[key] : "";
                        return $"\"{value?.ToString()?.Replace("\"", "\"\"") ?? ""}\"";
                    });
                    csv.AppendLine(string.Join(",", values));
                }

                return Encoding.UTF8.GetBytes(csv.ToString());
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出统计数据到CSV失败");
                throw;
            }
        }

        private static List<string> GetWeatherDataHeaders(IEnumerable<Dictionary<string, object>> data)
        {
            var headers = new List<string>();

            // 基础字段
            headers.AddRange(new[] { "id", "cityId", "cityName", "recordedAt", "createdAt", "source" });

            // 动态指标字段
            var dynamicFields = data
                .SelectMany(d => d.Keys)
                .Where(k => !headers.Contains(k))
                .Distinct()
                .OrderBy(k => k)
                .ToList();

            headers.AddRange(dynamicFields);

            return headers;
        }
    }
}